package newsDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import page.Page;

import usersDao.DBUtils;

public class MySqlNewsDao implements NewsDAO {

	@Override
	public int add(NewsEntity n) {
		String sql = "insert into `news`(nid,ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary) values (null,?,?,?,?,?,?,?,?)";
		return DBUtils.executeUpdate(sql, n.getNtid(), n.getNtitle(),
				n.getNauthor(), n.getNcreateDate(), n.getNpicPath(),
				n.getNcontent(), n.getNmodifyDate(), n.getNsummary());
	}

	@Override
	public int del(long nid) {
		String sql = "delete from news where nid=?";
		return DBUtils.executeUpdate(sql, nid);
	}

	@Override
	public int update(NewsEntity n) {
		String sql = "update news set ntid=?,ntitle=?,nauthor=?,npicPath=?,ncontent=?,nmodifyDate=?,nsummary=? where nid=?";
		return DBUtils.executeUpdate(sql,  n.getNtid(),n.getNtitle(), n.getNauthor(),
				 n.getNpicPath(), n.getNcontent(),
				n.getNmodifyDate(), n.getNsummary(),n.getNid());
	}

	@Override
	public NewsEntity find(long nid) {
		NewsEntity n = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select * from `news` where nid=?";
		Connection conn = null;
		try {
			conn = DBUtils.getConnection();
			st = conn.prepareStatement(sql);
			st.setLong(1, nid);
			rs = st.executeQuery();
			if (rs.next()) {
				n = new NewsEntity();
				n.setNid(rs.getLong("nid"));
				n.setNtid(rs.getLong("ntid"));
				n.setNtitle(rs.getString("ntitle"));
				n.setNauthor(rs.getString("nauthor"));
				n.setNcreateDate(rs.getTimestamp("ncreateDate"));
				n.setNpicPath(rs.getString("npicPath"));
				n.setNcontent(rs.getString("ncontent"));
				n.setNmodifyDate(rs.getTimestamp("nmodifyDate"));
				n.setNsummary(rs.getString("nsummary"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(rs, st, conn);
		}
		return n;
	}

	@Override
	public List<NewsEntity> findTop(long cid) {
		List<NewsEntity> list = new ArrayList<NewsEntity>();
		NewsEntity n = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select * from `News` where ntid=? order by nmodifyDate desc limit 0,6";
		Connection conn = null;
		try {
			conn = DBUtils.getConnection();
			st = conn.prepareStatement(sql);
			st.setLong(1, cid);
			rs = st.executeQuery();
			while (rs.next()) {
				n = new NewsEntity();
				n.setNid(rs.getLong("nid"));
				n.setNtid(rs.getLong("ntid"));
				n.setNtitle(rs.getString("ntitle"));
				n.setNauthor(rs.getString("nauthor"));
				n.setNcreateDate(rs.getTimestamp("ncreateDate"));
				n.setNpicPath(rs.getString("npicPath"));
				n.setNcontent(rs.getString("ncontent"));
				n.setNmodifyDate(rs.getTimestamp("nmodifyDate"));
				n.setNsummary(rs.getString("nsummary"));
				list.add(n);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(rs, st, conn);
		}
		return list;
	}

	@Override
	public List<NewsEntity> paging(long cid, int pageNo) {
		List<NewsEntity> list = new ArrayList<NewsEntity>();
		NewsEntity n = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select * from `news` where ntid=? limit ?,20";
		Connection conn = null;
		try {
			conn = DBUtils.getConnection();
			st = conn.prepareStatement(sql);
			st.setLong(1, cid);
			st.setLong(2, pageNo - 1);
			rs = st.executeQuery();
			while (rs.next()) {
				n = new NewsEntity();
				n.setNid(rs.getLong("nid"));
				n.setNtid(rs.getLong("ntid"));
				n.setNtitle(rs.getString("ntitle"));
				n.setNauthor(rs.getString("nauthor"));
				n.setNcreateDate(rs.getTimestamp("ncreateDate"));
				n.setNpicPath(rs.getString("npicPath"));
				n.setNcontent(rs.getString("ncontent"));
				n.setNmodifyDate(rs.getTimestamp("nmodifyDate"));
				n.setNsummary(rs.getString("nsummary"));
				list.add(n);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(rs, st, conn);
		}
		return list;
	}

	@Override
	public Page<NewsEntity> query(int pageNo) {
		Page<NewsEntity> p = new Page<NewsEntity>();
		p.setPageNo(pageNo);
		String sql = "select * from `news` order by ncreateDate desc limit ?,?";
		List<NewsEntity> list = new ArrayList<NewsEntity>();
		NewsEntity n = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		Connection conn = null;
		try {
			conn = DBUtils.getConnection();
			st = conn.prepareStatement(sql);
			st.setInt(1, (pageNo - 1) * Page.PAGESIZE);
			st.setInt(2, Page.PAGESIZE);
			rs = st.executeQuery();
			while (rs.next()) {
				n = new NewsEntity();
				n.setNid(rs.getInt("nid"));
				n.setNtid(rs.getInt("ntid"));
				n.setNtitle(rs.getString("ntitle"));
				n.setNauthor(rs.getString("nauthor"));
				n.setNcreateDate(rs.getTimestamp("ncreateDate"));
				n.setNpicPath(rs.getString("npicPath"));
				n.setNcontent(rs.getString("ncontent"));
				n.setNmodifyDate(rs.getTimestamp("nmodifyDate"));
				n.setNsummary(rs.getString("nsummary"));
				list.add(n);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(rs, st, null);
		}
		p.setDatas(list);
		String sql2 = "select count(*) from `news`";
		int total = 0;
		try {
			st = conn.prepareStatement(sql2);

			rs = st.executeQuery();
			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(rs, st, conn);
		}
		p.setTotalLines(total);
		return p;
	}

	@Override
	public Page<NewsEntity> search(long cid, int pageNo) {
		Page<NewsEntity> page=new Page<NewsEntity>();
		page.setPageNo(pageNo);
		Connection conn=null;
		PreparedStatement st=null;
		ResultSet rs=null;
		NewsEntity news=null;
		List<NewsEntity> list = new ArrayList<NewsEntity>();
		String sql="";
		try {
			conn=DBUtils.getConnection();
			st=conn.prepareStatement(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtils.close(rs, st, conn);	
		}
		
		return null;
	}

	@Override
	public List<NewsEntity> findAll() {
		NewsEntity n = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		List<NewsEntity> list = new ArrayList<NewsEntity>();
		String sql = "select * from `news`";
		Connection conn = null;
		try {
			conn = DBUtils.getConnection();
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			while (rs.next()) {
				n = new NewsEntity();
				n.setNid(rs.getInt("nid"));
				n.setNtid(rs.getInt("ntid"));
				n.setNtitle(rs.getString("ntitle"));
				n.setNauthor(rs.getString("nauthor"));
				n.setNcreateDate(rs.getTimestamp("ncreateDate"));
				n.setNpicPath(rs.getString("npicPath"));
				n.setNcontent(rs.getString("ncontent"));
				n.setNmodifyDate(rs.getTimestamp("nmodifyDate"));
				n.setNsummary(rs.getString("nsummary"));
				list.add(n);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(rs, st, conn);
		}
		return list;
	}
	
	public Page<NewsEntity> find(int pageNo, Integer tid) {
		Page<NewsEntity> p = new Page<NewsEntity>();
		p.setPageNo(pageNo);
		String sql = "select * from News " + (tid == null ? "" : " where ntid=" + tid)
				+ " ORDER BY nmodifyDate DESC  LIMIT ?,?";
		List<NewsEntity> datas = new ArrayList<NewsEntity>();
		// Èç¹û±íÎª¿Õ£¬·µ»Ø¿Õ¼¯ºÏ£¬²»ÊÇnull
		Connection con = DBUtils.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, (pageNo - 1) * Page.PAGESIZE);
			ps.setInt(2, Page.PAGESIZE);
			rs = ps.executeQuery();
			while (rs.next()) {
				// ¶ÁÈ¡Ò»ÐÐ£¬·â×°³ÉStudent¶ÔÏó£¬·ÅÈë¼¯ºÏ
				NewsEntity s = new NewsEntity();
				s.setNid(rs.getInt("nid"));
				s.setNtid(rs.getInt("ntid"));
				s.setNtitle(rs.getString("ntitle"));
				s.setNauthor(rs.getString("nauthor"));
				s.setNcreateDate(rs.getTimestamp("ncreateDate"));
				s.setNpicPath(rs.getString("npicPath"));
				s.setNcontent(rs.getString("ncontent"));
				s.setNmodifyDate(rs.getTimestamp("nmodifyDate"));
				s.setNsummary(rs.getString("nsummary"));
				datas.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(rs, ps, null);
		}
		p.setDatas(datas);
		// ×ÜÒ³Êý
		String sql2 = "select count(*) from News " + (tid == null ? "" : " where ntid=" + tid);
		int total = 0;
		try {
			ps = con.prepareStatement(sql2);

			rs = ps.executeQuery();
			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(rs, ps, con);
		}
		p.setTotalLines(total);
		return p;
	}


}
